﻿using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace mojoPortal.Web.RS.CRM.AppCode.Lib
{
    public class RSCRMDataLookupLoader : mojoPortal.Web.RS.AppCode.Lib.RSDataLookupLoader
    {

        public static void LoadAgentDDL(int companyid, DropDownList ddlAgent, string itemIndexZeroText = "All", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT EmployeeId, FirstName + ' ' + LastName AS AgentName FROM rs_tblEmployee WHERE CompanyId=@CompanyId ORDER BY FirstName, LastName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCRMCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);
            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            ddlAgent.Items.Clear();
            ddlAgent.DataSource = myDataReader;
            ddlAgent.DataTextField = "AgentName";
            ddlAgent.DataValueField = "EmployeeId";
            ddlAgent.DataBind();
            ddlAgent.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            myConnection.Close();
        }

        public static void LoadProviderDDL(int companyid, DropDownList ddlProvider, string itemIndexZeroText = "", string itemIndexZeroValue = "0")
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT * FROM rs_tblProvider WHERE CompanyId=@CompanyId ORDER BY ProviderName ASC";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCRMCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);
            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            ddlProvider.Items.Clear();
            ddlProvider.DataSource = myDataReader;
            ddlProvider.DataTextField = "ProviderName";
            ddlProvider.DataValueField = "ProviderID";
            ddlProvider.DataBind();
            ddlProvider.Items.Insert(0, new ListItem(itemIndexZeroText, itemIndexZeroValue));
            myConnection.Close();
        }

        public static void LoadProductByProviderID(int companyid, int providerid, CheckBoxList chkProduct)
        {
            SqlCommand myCommand;
            SqlDataReader myDataReader;
            String sqlSELECT = "SELECT prod.* FROM rs_tblProduct prod INNER JOIN rs_tblProvider prov ON prod.ProviderID=prov.ProviderID WHERE prod.ProviderID=@providerid AND prod.CompanyID=@companyid";
            SqlParameter param;
            SqlConnection myConnection;

            myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[RSCRMCommonFunctions.GetRSCompanyConnectionString()].ToString());
            myCommand = new SqlCommand(sqlSELECT, myConnection);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@companyid";
            param.Value = companyid;
            myCommand.Parameters.Add(param);

            param = new SqlParameter();
            param.SqlDbType = SqlDbType.Int;
            param.ParameterName = "@providerid";
            param.Value = providerid;
            myCommand.Parameters.Add(param);

            myConnection.Open();
            myDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
            chkProduct.Items.Clear();
            chkProduct.DataSource = myDataReader;
            chkProduct.DataTextField = "ProductName";
            chkProduct.DataValueField = "ProductID";
            chkProduct.DataBind();
            myConnection.Close();
        }


    }
}